package com.example.tool.controller;

import com.alibaba.fastjson.JSON;
import com.example.tool.controller.entity.*;
import com.hierynomus.protocol.commons.Charsets;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jcifs.smb.*;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Predicate;
import java.util.stream.Collectors;

@RestController
public class ToolController {
    private JdbcTemplate jdbcTemplate;
    private final String url = "jdbc:sqlserver://%s:%s;database=%s;encrypt=true;trustServerCertificate=true;";
    private final String smbUrl = "smb://%s:%s@%s%s";
    private volatile DatabaseConf databaseConf;
    private volatile ShareDirConf shareDirConf;
    private volatile SearchOutput searchOutput;

    @PostConstruct
    public void init() {
        Runnable runnable = () -> {
            try {
                File databaseConfFile = getDatabaseConfFile();
                if (databaseConfFile != null) {
                    DatabaseConf databaseConf = readDatabaseConf(databaseConfFile);
                    if (databaseConf.getIp() != null && databaseConf.getPort() != null && databaseConf.getUsername() != null && databaseConf.getPassword() != null && databaseConf.getDatabase() != null) {
                        configDatabase(databaseConf);
                    }

                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                File shareDirConfFile = getShareDirConfFile();
                if (shareDirConfFile != null) {
                    ShareDirConf shareDirConf = readShareDirConf(shareDirConfFile);
                    if (shareDirConf.getIp() != null && shareDirConf.getPassword() != null && shareDirConf.getUsername() != null) {
                        configShareDir(shareDirConf);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        };
        runnable.run();
    }

    private File getDatabaseConfFile() {
        String dir = System.getProperty("user.dir");
        File file = new File(dir);
        String parentPath = file.getParentFile().getAbsolutePath();
        String path = parentPath + File.separator + "conf" + File.separator + "database.cfg";
        File result = new File(path);
        if (!result.exists()) {
            try {
                result.createNewFile();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    private File getShareDirConfFile() {
        String dir = System.getProperty("user.dir");
        File file = new File(dir);
        String parentPath = file.getParentFile().getAbsolutePath();
        String path = parentPath + File.separator + "conf" + File.separator + "sharepath.cfg";
        File result = new File(path);
        if (!result.exists()) {
            try {
                result.createNewFile();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    private DatabaseConf readDatabaseConf(File f) {
        DatabaseConf databaseConf = new DatabaseConf();
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(f)));
            String line;
            while ((line = br.readLine()) != null) {
                String[] ss = line.split("=");
                if (ss[0].equals("ip")) {
                    databaseConf.setIp(ss[1]);
                } else if (ss[0].equals("port")) {
                    databaseConf.setPort(Integer.parseInt(ss[1]));
                } else if (ss[0].equals("username")) {
                    databaseConf.setUsername(ss[1]);
                } else if (ss[0].equals("password")) {
                    databaseConf.setPassword(ss[1]);
                } else if (ss[0].equals("database")) {
                    databaseConf.setDatabase(ss[1]);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return databaseConf;
    }

    private ShareDirConf readShareDirConf(File f) {
        ShareDirConf shareDirConf = new ShareDirConf();
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(f)));
            String line;
            while ((line = br.readLine()) != null) {
                String[] ss = line.split("=");
                if (ss[0].equals("ip")) {
                    shareDirConf.setIp(ss[1]);
                } else if (ss[0].equals("username")) {
                    shareDirConf.setUsername(ss[1]);
                } else if (ss[0].equals("password")) {
                    shareDirConf.setPassword(ss[1]);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return shareDirConf;
    }

    @PostMapping("/configDatabase")
    public HttpResponse<Boolean> configDatabase(@RequestBody DatabaseConf databaseConf) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(String.format(url, databaseConf.getIp(), databaseConf.getPort(), databaseConf.getDatabase()));
        config.setUsername(databaseConf.getUsername());
        config.setPassword(databaseConf.getPassword());
        config.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        // 可选配置参数
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setConnectionTimeout(10000);
        config.setValidationTimeout(5000);
        config.setLeakDetectionThreshold(30000);

        // 创建 HikariDataSource 实例
        HikariDataSource dataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //销毁旧的
        if (this.jdbcTemplate != null) {
            HikariDataSource ds = (HikariDataSource) this.jdbcTemplate.getDataSource();
            ds.close();
        }
        this.jdbcTemplate = jdbcTemplate;
        this.databaseConf = databaseConf;
        List<Integer> list = jdbcTemplate.query("select 1 as id", (resultSet, i) -> resultSet.getInt("id"));
        if (!CollectionUtils.isEmpty(list)) {
            File databaseConfFile = getDatabaseConfFile();
            PrintWriter pw = null;
            try {
                pw = new PrintWriter(new FileOutputStream(databaseConfFile), true);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                return HttpResponse.success(false);
            }
            pw.println("ip=" + databaseConf.getIp());
            pw.println("port=" + databaseConf.getPort());
            pw.println("username=" + databaseConf.getUsername());
            pw.println("password=" + databaseConf.getPassword());
            pw.println("database=" + databaseConf.getDatabase());
            return HttpResponse.success(true);
        } else {
            return HttpResponse.success(false);
        }
    }

    @GetMapping("getDatabaseConfig")
    public HttpResponse<DatabaseConf> getDatabaseConfig() {
        File databaseConfFile = getDatabaseConfFile();
        return HttpResponse.success(readDatabaseConf(databaseConfFile));
    }

    @PostMapping("/configShareDir")
    public HttpResponse<Boolean> configShareDir(@RequestBody ShareDirConf shareDirConf) {
        this.shareDirConf = shareDirConf;
        String remoteUrl = String.format(smbUrl, shareDirConf.getUsername(), URLEncoder.encode(shareDirConf.getPassword(), Charsets.UTF_8), shareDirConf.getIp(), "");
        try {
            SmbFile remoteFile = new SmbFile(remoteUrl);
            remoteFile.connect();//尝试连接
            if (remoteFile.exists()) {
                File shareDirConfFile = getShareDirConfFile();
                PrintWriter pw = new PrintWriter(new FileOutputStream(shareDirConfFile), true);
                pw.println("ip=" + shareDirConf.getIp());
                pw.println("username=" + shareDirConf.getUsername());
                pw.println("password=" + shareDirConf.getPassword());
                return HttpResponse.success(true);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return HttpResponse.success(false);
        }
        return HttpResponse.success(false);
    }

    @GetMapping("getShareDirConfig")
    public HttpResponse<DatabaseConf> getShareDirConfig() {
        File shareDirConfFile = getShareDirConfFile();
        return HttpResponse.success(readShareDirConf(shareDirConfFile));
    }

    @PostMapping("/search")
    public HttpResponse<SearchOutput> search(@RequestBody SearchInput searchInput) {
        if (this.searchOutput != null) {
            searchOutput.setHistoryData(true);
            return HttpResponse.success(this.searchOutput);
        }
        if (databaseConf == null || shareDirConf == null) {
            return HttpResponse.fail("请先维护数据库和共享文件配置");
        }
        String sql = "SELECT '' AS f1,\n" +
                "       '' AS f2,\n" +
                "       i.NPDsheet_no AS f3,\n" +
                "\t   (CASE WHEN i.NPDmachine_name = 'EM3510-TK' THEN '1'\n" +
                "\t         WHEN i.NPDmachine_name = 'EMLZ12AJ-TK' THEN '10'\n" +
                "\t   END) AS f4,\n" +
                "       i.NPDmachine_name AS f5,\n" +
                "\t   i.NPDsheet_code AS f6,\n" +
                "\t   '' AS f7,\n" +
                "\t   '1' AS f8,\n" +
                "\t   '1' AS f9,\n" +
                "\t   '1' AS f10,\n" +
                "\t   '' AS f11,\n" +
                "\t   '0' AS f12,\n" +
                "\t   '0' AS f13,\n" +
                "\t   '0' AS f14,\n" +
                "\t   '0' AS f15,\n" +
                "\t   '0' AS f16,\n" +
                "\t   '0' AS f17,\n" +
                "\t   '0' AS f18,\n" +
                "\t   '0' AS f19,\n" +
                "\t   f.NPDfile_name f20,\n" +
                "\t   i.NPDnc_modify_date f21\n" +
                "  FROM TB_NPDIF i INNER JOIN TB_NPDFN f ON i.NPDsheet_no = f.NPDsheet_no AND i.NPDmachine_name = f.NPDmachine_name AND f.NPDfile_type = '4002'\n" +
                " WHERE 1=1";
        if (!StringUtils.isEmpty(searchInput.getPartNo())) {
            sql = sql + " AND i.NPDsheet_no like '%" + searchInput.getPartNo().trim() + "%'\n";
        }
        if (!StringUtils.isEmpty(searchInput.getMachineName())) {
            sql = sql + " AND i.NPDmachine_name = '" + searchInput.getMachineName().trim() + "'\n";
        }
        if (!StringUtils.isEmpty(searchInput.getMaterialCode())) {
            sql = sql + " AND i.NPDsheet_code = '" + searchInput.getMaterialCode().trim() + "'\n";
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
        if (searchInput.getStartUpdateTime() != null) {
            sql = sql + " AND i.NPDnc_modify_date >= '" + simpleDateFormat.format(searchInput.getStartUpdateTime()) + "'\n";
        }
        if (searchInput.getEndUpdateTime() != null) {
            sql = sql + " AND i.NPDnc_modify_date <= '" + simpleDateFormat.format(searchInput.getEndUpdateTime()) + "'";
        }
        System.out.println("sql : "+sql);
        AtomicInteger atomicInteger = new AtomicInteger(1);
        long l = System.currentTimeMillis();
        List<SearchOutput.Data1> list = jdbcTemplate.query(sql, (rs, rowNum) -> {
            Map<String, String> map = new HashMap<>();
            for (int i = 1; i <= 21; i++) {
                String fieldName = "f" + i;
                String fieldValue = rs.getString(fieldName);
                map.put(fieldName, fieldValue);
            }
            SearchOutput.Data1 data1 = JSON.parseObject(JSON.toJSONString(map), SearchOutput.Data1.class);
            data1.setSerialNumber(atomicInteger.getAndIncrement());
            return data1;
        });
        System.out.println("sql执行耗时 "+(System.currentTimeMillis()-l)+" 毫秒,返回 "+list.size()+" 条数据");
        for (SearchOutput.Data1 data1 : list) {
            try {
                String f20 = data1.getF20();
                f20 = f20.replace("\\\\", "");
                f20 = f20.substring(f20.indexOf("\\")).replace("\\", "/");
                f20 = String.format(smbUrl, shareDirConf.getUsername(), URLEncoder.encode(shareDirConf.getPassword(), Charsets.UTF_8), shareDirConf.getIp(), f20);
                data1.setF20(f20);
                l = System.currentTimeMillis();
                List<String> lines = getRemoteFile(f20);
                System.out.println("读取文件信息耗时 "+(System.currentTimeMillis()-l)+" 毫秒,文件路径为 "+f20);
                setChildData(data1, lines);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        SearchOutput searchOutput = new SearchOutput();
        searchOutput.setData(list);
        searchOutput.setHistoryData(false);
        if (!CollectionUtils.isEmpty(list)) {
            this.searchOutput = searchOutput;
        }
        return HttpResponse.success(searchOutput);
    }

    @PostMapping("/removeSearchData")
    public HttpResponse<Boolean> removeSearchData(@RequestBody Map map) {
        this.searchOutput = null;
        return HttpResponse.success(null);
    }

    @PostMapping("/updateSearchData")
    public HttpResponse<Boolean> updateSearchData(@RequestBody SearchOutput searchOutput) {
        this.searchOutput = searchOutput;
        return HttpResponse.success(null);
    }

    @PostMapping("/export")
    public ResponseEntity<byte[]> export(@RequestBody List<Integer> serialNumbers) {
        SearchOutput searchOutput = this.searchOutput;
        List<SearchOutput.Data1> exportData = searchOutput.getData().stream()
                .filter(data1 -> serialNumbers.contains(data1.getSerialNumber()))
                .filter(data1 -> NumberUtils.isNumber(data1.getF7()))
                .collect(Collectors.toList());
        // 2. 创建Excel工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("排版基本信息");

            // 3. 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {
                    "[(37)产品代码]", "[(38)套件代码]", "[(08)零件编号]",
                    "[(02)设备名称]", "[(13)基准机床名称]", "[(14)材料代码",
                    "[排版零件总数]", "[(09)制造数量]", "[(01)登记目标调度分类]",
                    "[(34)堆叠托盘编号]", "[(36)主架托盘编号]", "[(23)主架托盘更换标志]",

                    "[(26)堆叠方向]", "[(27)外围装置-1]", "[(28)外围装置-2]",
                    "[(29)外围装置-3]", "[(30)卸载超驰]", "[(31)有无分离]",
                    "[(32)分离时间]"
            };
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
            }

            // 4. 填充数据
            int rowNum = 1;
            for (SearchOutput.Data1 data : exportData) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(data.getF1().trim());
                row.createCell(1).setCellValue(data.getF2().trim());
                row.createCell(2).setCellValue(data.getF3().trim());
                row.createCell(3).setCellValue(data.getF4().trim());
                row.createCell(4).setCellValue(data.getF5().trim());
                row.createCell(5).setCellValue(data.getF6().trim());
                row.createCell(6).setCellValue(data.getF7().trim());

                row.createCell(7).setCellValue(data.getF8().trim());
                row.createCell(8).setCellValue(data.getF9().trim());
                row.createCell(9).setCellValue(data.getF10().trim());
                row.createCell(10).setCellValue(data.getF11().trim());
                row.createCell(11).setCellValue(data.getF12().trim());
                row.createCell(12).setCellValue(data.getF13().trim());
                row.createCell(13).setCellValue(data.getF14().trim());

                row.createCell(14).setCellValue(data.getF15().trim());
                row.createCell(15).setCellValue(data.getF16().trim());
                row.createCell(16).setCellValue(data.getF17().trim());
                row.createCell(17).setCellValue(data.getF18().trim());
                row.createCell(18).setCellValue(data.getF19().trim());
            }
            // 5. 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            sheet = workbook.createSheet("排版打印信息");

            headerRow = sheet.createRow(0);
            String[] headers2 = {
                    "[(37)产品代码]", "[(38)套件代码]", "[(08)零件编号]",
                    "[(03)排版零件号]", "[(04)个数]", "[(05)IJP打印信息]"

            };
            for (int i = 0; i < headers2.length; i++) {
                headerRow.createCell(i).setCellValue(headers2[i]);
            }

            rowNum = 1;
            for (SearchOutput.Data1 data : exportData) {
                List<SearchOutput.Data2> childs = data.getChilds();
                for (SearchOutput.Data2 child : childs) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(child.getCf1().trim());
                    row.createCell(1).setCellValue(child.getCf2().trim());
                    row.createCell(2).setCellValue(child.getCf3().trim());
                    row.createCell(3).setCellValue(child.getCf5().trim());
                    row.createCell(4).setCellValue(child.getCf6().trim());
                    row.createCell(5).setCellValue(child.getCf7().trim());
                }
            }

            // 5. 自动调整列宽
            for (int i = 0; i < headers2.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // 6. 将工作簿写入字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);

            // 7. 设置响应头
            HttpHeaders responseHeaders = new HttpHeaders();
            responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            responseHeaders.setContentDisposition(
                    ContentDisposition.builder("attachment")
                            .filename("export_data.xlsx")
                            .build());

            return ResponseEntity.ok()
                    .headers(responseHeaders)
                    .body(outputStream.toByteArray());

        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }


    private List<String> getRemoteFile(String remoteUrl) {
        List<String> lines = new ArrayList<>();
        try {
            SmbFile remoteFile = new SmbFile(remoteUrl);
            remoteFile.connect();//尝试连接
            if (remoteFile.exists()) {
                try (SmbFileInputStream smbIn = new SmbFileInputStream(remoteFile);
                     InputStreamReader isr = new InputStreamReader(smbIn, StandardCharsets.UTF_8);
                     BufferedReader br = new BufferedReader(isr)) {
                    String line;
                    while ((line = br.readLine()) != null) {
                        lines.add(line.trim());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lines;
    }

    private void setChildData(SearchOutput.Data1 data1, List<String> lines) {
        Map<String, SearchOutput.Data2> map = new HashMap<>();
        for (int i = 0; i < lines.size(); i++) {
            String line = lines.get(i);
            if (line.startsWith("[HEADER_INFO]")) {
                i = handleHeaderInfo(data1, lines, i);
            } else if (line.startsWith("[PD_")) {
                handlePd(data1, lines, i, map);
            }
        }
    }

    private int handleHeaderInfo(SearchOutput.Data1 data1, List<String> lines, int i) {
        for (int j = i + 1; j < lines.size(); j++) {
            String line = lines.get(j);
            if (line.startsWith("PDNUM=")) {
                String[] ss = line.split("=");
                data1.setF7(ss[1]);
                return j;
            }
        }
        return lines.size();
    }

    private int handlePd(SearchOutput.Data1 data1, List<String> lines, int i, Map<String, SearchOutput.Data2> map) {
        SearchOutput.Data2 data2 = new SearchOutput.Data2();
        data2.setCf1(data1.getF1());
        data2.setCf2(data1.getF2());
        data2.setCf3(data1.getF3());

        int setCount = 0;
        for (int j = i + 1; j < lines.size(); j++) {
            String line = lines.get(j);
            if (line.startsWith("HNO=")) {
                String[] ss = line.split("=");
                data2.setCf4(ss[1]);
                setCount++;
            } else if (line.startsWith("HNAME=")) {
                String[] ss = line.split("=");
                data2.setCf5(ss[1]);
                setCount++;
            } else if (line.startsWith("INUM=")) {
                //小件的零件个数一定是1
                data2.setCf6(Integer.valueOf(1).toString());
                setCount++;
            } else if (line.startsWith("ISTR1=")) {
                String[] ss = line.split("=");
                if (ss[1].contains("/")) {
                    data2.setCf7(ss[1].substring(ss[1].indexOf("/") + 1));
                }

                setCount++;
            }
            if (setCount == 4) {
                SearchOutput.Data2 oldData2 = null;
                if ((oldData2 = map.get(data2.getCf5())) != null) {
                    oldData2.setCf6(Integer.valueOf(Integer.parseInt(oldData2.getCf6()) + 1).toString());
                } else {
                    map.put(data2.getCf5(), data2);
                    data1.getChilds().add(data2);
                }
                return j;
            }
        }
        return lines.size();
    }
}
